﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using E.Enum;
using X.DALSqlServer;

namespace E.DAL
{
    public class SurveyDAL
    {
        /// <summary>
        /// 获取问卷选项类别列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetSurveyCategoryList()
        {
            IDictionary<int, string> list = new Dictionary<int,string>();
            string sql = "SELECT ID, Name FROM E_SurveyCategory";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取已存在课程问卷列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetExistCourseSurveyList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT a.ID, b.Name FROM E_CourseSurvey AS a INNER JOIN E_Dict_Course AS b ON a.CourseID = b.ID WHERE b.DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取已存在认证或项目问卷列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetExistCertificateSurveyList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT a.ID, b.Name FROM E_CertificateSurvey AS a INNER JOIN E_Dict_Certificate AS b ON a.CertificateID = b.ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未设置问卷课程列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetNotSetCourseSurveyList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE (ID NOT IN (SELECT CourseID FROM E_CourseSurvey)) AND (DelFlag = 0)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未设置问卷认证或项目列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetNotSetCertificateSurveyList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE (ID NOT IN (SELECT CertificateID FROM E_CertificateSurvey))";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取课程问卷设置信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SurveySettingModel GetCourseSurveySetting(int id)
        {
            SurveySettingModel model = new SurveySettingModel();
            string sql = "SELECT b.ID, b.Name FROM E_CourseSurvey AS a INNER JOIN E_Dict_Course AS b ON a.CourseID = b.ID WHERE a.ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.CID = (int)dr["ID"];
                    model.CName = dr["Name"].ToString();
                    model.OptionIDList = GetCourseSurveyOptionList(id);
                }
                else
                    model.CID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取课程问卷设置选项信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<int> GetCourseSurveyOptionList(int id)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT OptionID FROM E_CourseSurveySetting WHERE CourseSurveyID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["OptionID"]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证或项目问卷设置信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SurveySettingModel GetCertificateSurveySetting(int id)
        {
            SurveySettingModel model = new SurveySettingModel();
            string sql = "SELECT b.ID, b.Name FROM E_CertificateSurvey AS a INNER JOIN E_Dict_Certificate AS b ON a.CertificateID = b.ID WHERE a.ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.CID = (int)dr["ID"];
                    model.CName = dr["Name"].ToString();
                    model.OptionIDList = GetCertificateSurveyOptionList(id);
                }
                else
                    model.CID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取认证或项目问卷设置选项信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<int> GetCertificateSurveyOptionList(int id)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT OptionID FROM E_CertificateSurveySetting WHERE CertificateSurveyID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr["OptionID"]);
                }
                return list;
            }
        }

        /// <summary>
        /// 根据类别ID获取选项列表
        /// </summary>
        /// <param name="categoryID"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetSurveyOptionListByCategoryID(int categoryID)
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID,Name FROM E_SurveyOption WHERE CategoryID = " + categoryID;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取选项信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SurveyOptionModel GetSurveyOptionInfoById(int id)
        {
            SurveyOptionModel model = new SurveyOptionModel();
            string sql = "SELECT * FROM E_SurveyOption WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CategoryID = (int)dr["CategoryID"];
                    model.Name = dr["Name"].ToString();
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 添加问卷选项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddOption(SurveyOptionModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Name", model.Name)
            };
            string sql = "INSERT INTO E_SurveyOption(CategoryID,Name)VALUES(@CategoryID,@Name)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 更新问卷选项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpateOption(SurveyOptionModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Name", model.Name)
            };
            string sql = "UPDATE E_SurveyOption SET CategoryID = @CategoryID, Name = @Name WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 检查选项ID是否存在于课程问卷设置中
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckOptionIDInCourseSurveySetting(string[] id)
        {
            string sql = "SELECT ID FROM E_CourseSurveySetting WHERE " + DALUtils.GetInCondition("OptionID", id);
            return SqlHelper.Exists(sql);
        }

        /// <summary>
        /// 检查选项ID是否存在于认证或项目问卷设置中
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool CheckOptionIDInCertificateSurveySetting(string[] id)
        {
            string sql = "SELECT ID FROM E_CertificateSurveySetting WHERE " + DALUtils.GetInCondition("OptionID", id);
            return SqlHelper.Exists(sql);
        }

        /// <summary>
        /// 删除问卷选项
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelOption(string[] id)
        {
            string sql = "DELETE FROM E_SurveyOption WHERE " + DALUtils.GetInCondition("ID", id);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取问卷选项列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public SurveyOptionListModel GetSurveyOptionListByCondition(SearchSurveyOptionModel searchModel, int pageSize, int pageIndex)
        {
            SurveyOptionListModel listModel = new SurveyOptionListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CategoryID", searchModel.CategoryID),
                new SqlParameter("@Name", searchModel.Name),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<SurveyOptionModel> list = new List<SurveyOptionModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getSurveyOptionListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        SurveyOptionModel model = new SurveyOptionModel();
                        model.ID = (int)dr["ID"];
                        model.Name = dr["Name"].ToString();
                        model.CategoryName = dr["CategoryName"].ToString();
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取所有问卷选项列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetAllSurveyOptionList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID,Name,CategoryName FROM v_surveyoption ORDER BY ID,CategoryID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], "[" + dr[2].ToString() + "]" + dr[1].ToString());
                }
                return list;
            }
        }

        #region 课程

        /// <summary>
        /// 添加课程问卷调查
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCourseSurveyLog(SurveyLogModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", model.UserID),
                new SqlParameter("@SurveyID", model.SurveyID),
                new SqlParameter("@SurveyContent", model.SurveyContent),
                new SqlParameter("@TotalComment", model.Comment),
                new SqlParameter("@SubmitTime", model.SubmitTime)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_addCourseSurveyLog", parms);
        }

        /// <summary>
        /// 获取课程问卷调查信息
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public CourseSurveyModel GetCourseSurveyInfo(int examid, int userid)
        {
            CourseSurveyModel model = new CourseSurveyModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ExamID", examid),
                new SqlParameter("@UserID", userid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getCourseSurveyInfo", parms))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.OptionList = GetCourseSurveyCategoryList(model.ID);
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        public int GetCourseIDByExamID(int examid)
        {
            int i = -1;
            string sql = "SELECT ISNULL(CourseCertificateID,-1) AS CourseCertificateID FROM [dbo].[E_Exam] WHERE [ID]=" + examid.ToString();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    i = (int)dr["CourseCertificateID"];
                }
            }
            return i;
        }

        /// <summary>
        /// 获取课程问卷调查信息（内训）
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public CourseSurveyModel GetCourseSurveyInfo_Internal(int examid, int userid)
        {
            CourseSurveyModel model = new CourseSurveyModel();
            int courseid = GetCourseIDByExamID(examid);
            string sql = "SELECT a.ID, a.CourseID, b.Name AS CourseName, e.Name AS TeacherName FROM E_CourseSurvey AS a INNER JOIN E_Dict_Course AS b ON a.CourseID = b.ID INNER JOIN E_Internal_Class AS c ON b.ID = c.CourseID INNER JOIN E_Internal_Class2User AS d ON c.ID = d.ClassID INNER JOIN E_Teacher AS e ON c.TeacherID = e.ID WHERE (a.CourseID = " + courseid + ") AND (d.UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.OptionList = GetCourseSurveyCategoryList(model.ID);
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取课程问卷调查信息（内训公共课）
        /// </summary>
        /// <param name="examid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public CourseSurveyModel GetCourseSurveyInfo_Internal_P(int examid, int userid)
        {
            CourseSurveyModel model = new CourseSurveyModel();
            //int surveyid = GetSurveyIDByExamID(examid);
            string sql = "SELECT a.ID, a.CourseID, b.Name AS CourseName FROM E_CourseSurvey AS a INNER JOIN E_Dict_Course AS b ON a.CourseID = b.ID INNER JOIN E_Public_Class AS c ON b.ID = c.CourseID INNER JOIN E_Public_Class2User AS d ON c.ID = d.ClassID WHERE (a.CourseID = " + examid + ") AND (d.UserID = " + userid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.TeacherName = "";
                    model.OptionList = GetCourseSurveyCategoryList(model.ID);
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取课程问卷调查类别及选项列表（问卷ID）
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<SurveyCategoryAndOptionModel> GetCourseSurveyCategoryList(int surveyid)
        {
            IList<SurveyCategoryAndOptionModel> categoryList = new List<SurveyCategoryAndOptionModel>();
            string sql = "SELECT DISTINCT CategoryID, CategoryName FROM v_coursesurvey WHERE (CourseSurveyID = " + surveyid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    SurveyCategoryAndOptionModel model = new SurveyCategoryAndOptionModel();
                    model.ID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.OptionList = GetCourseSurveyOptionListByCategoryID(model.ID, surveyid);
                    categoryList.Add(model);
                }
                return categoryList;
            }
        }

        /// <summary>
        /// 获取课程问卷调查类别及选项列表（课程ID）
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<SurveyCategoryAndOptionModel> GetCourseSurveyCategoryListByCourseID(int courseid)
        {
            IList<SurveyCategoryAndOptionModel> categoryList = new List<SurveyCategoryAndOptionModel>();
            string sql = "SELECT DISTINCT a.CategoryID, a.CategoryName, a.CourseSurveyID FROM v_coursesurvey a INNER JOIN E_CourseSurvey b ON a.CourseSurveyID = b.ID WHERE (b.CourseID = " + courseid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    SurveyCategoryAndOptionModel model = new SurveyCategoryAndOptionModel();
                    model.ID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.OptionList = GetCourseSurveyOptionListByCategoryID(model.ID, (int)dr["CourseSurveyID"]);
                    categoryList.Add(model);
                }
                return categoryList;
            }
        }

        /// <summary>
        /// 获取课程选项列表
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetCourseSurveyOptionListByCategoryID(int categoryid, int surveyid)
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT OptionID, OptionName FROM v_coursesurvey WHERE (CourseSurveyID = " + surveyid + ") AND (CategoryID = " + categoryid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未参与课程问卷调查列表
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetUnattendedCourseSurveyList(int userid)
        {
            string sql = " SELECT b.ExamID, d.Name AS CourseName FROM E_TestLog AS a INNER JOIN E_Test AS b ON a.TestID = b.ID INNER JOIN E_Exam AS c ON b.ExamID = c.ID INNER JOIN E_Dict_Course AS d ON c.CourseCertificateID = d.ID WHERE (a.UserID = " + userid + ") AND (c.CategoryID = 1) AND (a.IsOver = 1) AND (c.CourseCertificateID NOT IN (SELECT b.CourseID FROM E_CourseSurveyLog AS a INNER JOIN E_CourseSurvey AS b ON a.SurveyID = b.ID WHERE (a.UserID = " + userid + ")))";
            IDictionary<int, string> list = new Dictionary<int, string>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未参与课程问卷调查列表（内训）
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetUnattendedCourseSurveyList_Internal(int userid)
        {
            string sql = "SELECT d.ID, c.Name AS CourseName FROM E_Internal_Class2User AS a INNER JOIN E_Internal_Class AS b ON a.ClassID = b.ID INNER JOIN E_Dict_Course AS c ON b.CourseID = c.ID INNER JOIN E_CourseSurvey AS d ON c.ID = d.CourseID WHERE (a.UserID = " + userid + ") AND (c.DelFlag = 0) AND (d.ID NOT IN (SELECT SurveyID FROM E_CourseSurveyLog WHERE (UserID = " + userid + ")))";
            IDictionary<int, string> list = new Dictionary<int, string>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未参与课程问卷调查列表（内训公共课）
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetUnattendedCourseSurveyList_Internal_P(int userid)
        {
            string sql = "SELECT d.ID, c.Name AS CourseName FROM E_Public_Class2User AS a INNER JOIN E_Public_Class AS b ON a.ClassID = b.ID INNER JOIN E_Dict_Course AS c ON b.CourseID = c.ID INNER JOIN E_CourseSurvey AS d ON c.ID = d.CourseID WHERE (a.UserID = " + userid + ") AND (c.DelFlag = 0) AND (d.ID NOT IN (SELECT SurveyID FROM E_CourseSurveyLog WHERE (UserID = " + userid + ")))";
            IDictionary<int, string> list = new Dictionary<int, string>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取未参与项目问卷调查列表（内训）
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetUnattendedCertificateSurveyList_Internal(int userid)
        {
            string sql = "SELECT  c.ID, b.Name AS CertificateName FROM E_User AS a INNER JOIN E_Dict_Certificate AS b ON a.CurrentCertificateID = b.ID INNER JOIN E_CertificateSurvey AS c ON b.ID = c.CertificateID WHERE (a.ID = " + userid + ") AND (c.ID NOT IN (SELECT SurveyID FROM E_CertificateSurveyLog WHERE (UserID = " + userid + ")))";
            IDictionary<int, string> list = new Dictionary<int, string>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        public int GetCourseSurveyAttendedUserCountByClassID(int classid)
        {
            string sql = "SELECT COUNT(UserID) AS Count FROM E_CourseSurveyLog WHERE (SurveyID = (SELECT ID FROM E_CourseSurvey WHERE (CourseID = (SELECT b.CourseID FROM E_Class AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.CourseRelationID = b.ID WHERE(a.ID = " + classid + "))))) AND (UserID IN (SELECT UserID FROM E_Class2User WHERE (ClassID = " + classid + ")))";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        public int GetInternalCourseSurveyAttendedUserCountByClassID(int classid)
        {
            string sql = "SELECT COUNT(UserID) AS Count FROM E_CourseSurveyLog WHERE (SurveyID = (SELECT ID FROM E_CourseSurvey WHERE (CourseID = (SELECT b.CourseID FROM E_Class AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.CourseRelationID = b.ID WHERE(a.ID = " + classid + "))))) AND (UserID IN (SELECT UserID FROM E_Internal_Class2User WHERE (ClassID = " + classid + ")))";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        public int GetPublicCourseSurveyAttendedUserCountByClassID(int classid)
        {
            string sql = "SELECT COUNT(UserID) AS Count FROM E_CourseSurveyLog WHERE (SurveyID = (SELECT ID FROM E_CourseSurvey WHERE (CourseID = (SELECT b.CourseID FROM E_Class AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.CourseRelationID = b.ID WHERE(a.ID = " + classid + "))))) AND (UserID IN (SELECT UserID FROM E_Public_Class2User WHERE (ClassID = " + classid + ")))";
            return Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
        }

        /// <summary>
        /// 添加课程问卷设置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCourseSurveySetting(SurveySettingModel model)
        {
            string sql = "INSERT INTO E_CourseSurvey(CourseID)VALUES(" + model.CID + "); SELECT @@IDENTITY";
            int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
            IList<string> sqlList = new List<string>();
            CourseSettingHandle(id, model.OptionIDStrList, sqlList);
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 更新课程问卷设置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateCourseSurveySetting(SurveySettingModel model)
        {
            IList<string> sqlList = new List<string>();
            CourseSettingHandle(model.ID, model.OptionIDStrList, sqlList);
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 删除课程问卷
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCourseSurvey(string[] id)
        {
            string sql = "DELETE FROM E_CourseSurvey WHERE " + DALUtils.GetInCondition("ID", id) + "; DELETE FROM E_CourseSurveySetting WHERE " + DALUtils.GetInCondition("CourseSurveyID", id);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 课程问卷设置处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="optionid"></param>
        /// <returns></returns>
        private void CourseSettingHandle(int id, string[] optionid, IList<string> sqllist)
        {
            sqllist.Add("DELETE FROM E_CourseSurveySetting WHERE CourseSurveyID = " + id);
            foreach (string s in optionid)
            {
                sqllist.Add("INSERT INTO E_CourseSurveySetting(CourseSurveyID, OptionID)VALUES(" + id + "," + s + ");");
            }
        }

        #endregion

        #region 认证
        
        /// <summary>
        /// 获取认证问卷调查信息
        /// </summary>
        /// <param name="examid"></param>
        /// <returns></returns>
        public CertificateSurveyModel GetCertificateSurveyInfo(int examid)
        {
            CertificateSurveyModel model = new CertificateSurveyModel();
            string sql = "SELECT a.ID, a.CertificateID, b.Name AS CertificateName FROM E_CertificateSurvey AS a INNER JOIN E_Dict_Certificate AS b ON a.CertificateID = b.ID WHERE (a.CertificateID = (SELECT CourseCertificateID FROM E_Exam WHERE (ID = " + examid + ")))";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.OptionList = GetCertificateSurveyCategoryList(model.ID);
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 获取认证问卷调查信息（内训）
        /// </summary>
        /// <param name="surveyid"></param>
        /// <returns></returns>
        public CertificateSurveyModel GetCertificateSurveyInfo_Internal(int surveyid)
        {
            CertificateSurveyModel model = new CertificateSurveyModel();
            string sql = "SELECT a.ID, a.CertificateID, b.Name AS CertificateName FROM E_CertificateSurvey AS a INNER JOIN E_Dict_Certificate AS b ON a.CertificateID = b.ID WHERE (a.ID = " + surveyid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CertificateName = dr["CertificateName"].ToString();
                    model.OptionList = GetCertificateSurveyCategoryList(model.ID);
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 添加认证问卷调查
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCertificateSurveyLog(SurveyLogModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", model.UserID),
                new SqlParameter("@SurveyID", model.SurveyID),
                new SqlParameter("@SurveyContent", model.SurveyContent),
                new SqlParameter("@TotalComment", model.Comment),
                new SqlParameter("@SubmitTime", model.SubmitTime)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_addCertificateSurveyLog", parms);
        }

        /// <summary>
        /// 获取认证调查信息
        /// </summary>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public CertificateSurveyReportModel GetCertificateSurveyReport(int certificateid)
        {
            CertificateSurveyReportModel model = new CertificateSurveyReportModel();
            string sql = "SELECT a.ID, b.Name AS CertficateName FROM E_CertificateSurvey AS a INNER JOIN E_Dict_Certificate AS b ON a.CertificateID = b.ID WHERE(a.CertificateID = " + certificateid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr[0];
                    model.CertificateName = dr[1].ToString();
                    model.OptionList = GetCertificateSurveyCategoryList(model.ID);
                    model.VoteList = GetCertificateVoteInfo(model.ID);
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取认证选项列表
        /// </summary>
        /// <param name="categoryid"></param>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public IDictionary<int, string> GetCertificateSurveyOptionListByCategoryID(int categoryid, int surveyid)
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT OptionID, OptionName FROM v_certificatesurvey WHERE (CertificateSurveyID = " + surveyid + ") AND (CategoryID = " + categoryid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证问卷调查类别及选项列表
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<SurveyCategoryAndOptionModel> GetCertificateSurveyCategoryList(int surveyid)
        {
            IList<SurveyCategoryAndOptionModel> categoryList = new List<SurveyCategoryAndOptionModel>();
            string sql = "SELECT DISTINCT CategoryID, CategoryName FROM v_certificatesurvey WHERE (CertificateSurveyID = " + surveyid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    SurveyCategoryAndOptionModel model = new SurveyCategoryAndOptionModel();
                    model.ID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.OptionList = GetCertificateSurveyOptionListByCategoryID(model.ID, surveyid);
                    categoryList.Add(model);
                }
                return categoryList;
            }
        }

        /// <summary>
        /// 获取调查信息
        /// </summary>
        /// <param name="surveyid"></param>
        /// <returns></returns>
        public IDictionary<string, string> GetCertificateVoteInfo(int surveyid)
        {
            IDictionary<string, string> list = new Dictionary<string, string>();
            string sql = "SELECT SurveyContent, TotalComment FROM E_CertificateSurveyLog WHERE (SurveyID = " + surveyid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString(), dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 添加认证或项目问卷设置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCertificateSurveySetting(SurveySettingModel model)
        {
            string sql = "INSERT INTO E_CertificateSurvey(CertificateID)VALUES(" + model.CID + "); SELECT @@IDENTITY";
            int id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql));
            IList<string> sqlList = new List<string>();
            CertificateSettingHandle(id, model.OptionIDStrList, sqlList);
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 更新认证或项目问卷设置
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateCertificateSurveySetting(SurveySettingModel model)
        {
            IList<string> sqlList = new List<string>();
            CertificateSettingHandle(model.ID, model.OptionIDStrList, sqlList);
            return SqlHelper.ExecuteSqlTran(sqlList);
        }

        /// <summary>
        /// 删除认证或项目问卷
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCertificateSurvey(string[] id)
        {
            string sql = "DELETE FROM E_CertificateSurvey WHERE " + DALUtils.GetInCondition("ID", id) + "; DELETE FROM E_CertificateSurveySetting WHERE " + DALUtils.GetInCondition("CertificateSurveyID", id);
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 认证或项目问卷设置处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="optionid"></param>
        /// <returns></returns>
        private void CertificateSettingHandle(int id, string[] optionid, IList<string> sqllist)
        {
            sqllist.Add("DELETE FROM E_CertificateSurveySetting WHERE CertificateSurveyID = " + id);
            foreach (string s in optionid)
            {
                sqllist.Add("INSERT INTO E_CertificateSurveySetting(CertificateSurveyID, OptionID)VALUES(" + id + "," + s + ");");
            }
        }

        #endregion

        #region Analysis

        public ClassUserCountListModel GetClassUserCountList(CourseAnalysisSearchModel searchModel, int pageSize, int pageIndex)
        {
            ClassUserCountListModel listModel = new ClassUserCountListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@TeacherName", searchModel.TeacherName),
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ClassUserCountModel> list = new List<ClassUserCountModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getSurveyClassInfoList", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ClassUserCountModel model = new ClassUserCountModel();
                        model.ClassID = (int)dr["ID"];
                        model.ClassCode = dr["ClassCode"].ToString();
                        model.TeacherName = dr["TeacherName"].ToString();
                        model.CourseName = dr["CourseName"].ToString();
                        model.CertificateName = dr["CertificateName"].ToString();
                        model.AreaName = dr["AreaName"].ToString();
                        model.UserCount = (int)dr["UserCount"];
                        model.AttendedUserCount = GetCourseSurveyAttendedUserCountByClassID(model.ClassID);
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        public ClassUserCountListModel GetInternalClassUserCountList(CourseAnalysisSearchModel searchModel, int pageSize, int pageIndex)
        {
            ClassUserCountListModel listModel = new ClassUserCountListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ClassUserCountModel> list = new List<ClassUserCountModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getSurveyClassInfoList", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ClassUserCountModel model = new ClassUserCountModel();
                        model.ClassID = (int)dr["ID"];
                        model.ClassCode = dr["ClassCode"].ToString();
                        model.CourseName = dr["CourseName"].ToString();
                        model.CertificateName = dr["CertificateName"].ToString();
                        model.UserCount = (int)dr["UserCount"];
                        model.AttendedUserCount = GetInternalCourseSurveyAttendedUserCountByClassID(model.ClassID);
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        public ClassUserCountListModel GetPublicClassUserCountList(CourseAnalysisSearchModel searchModel, int pageSize, int pageIndex)
        {
            ClassUserCountListModel listModel = new ClassUserCountListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ClassUserCountModel> list = new List<ClassUserCountModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_public_getSurveyClassInfoList", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ClassUserCountModel model = new ClassUserCountModel();
                        model.ClassID = (int)dr["ID"];
                        model.ClassCode = dr["ClassCode"].ToString();
                        model.CourseName = dr["CourseName"].ToString();
                        model.UserCount = (int)dr["UserCount"];
                        model.AttendedUserCount = GetPublicCourseSurveyAttendedUserCountByClassID(model.ClassID);
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        public IList<CourseSurveyReportModel> GetCourseAnalysisInfo(CourseAnalysisSearchModel searchModel)
        {
            IList<CourseSurveyReportModel> list = new List<CourseSurveyReportModel>();
            IDataParameter[] parms = new IDataParameter[]{
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@TeacherName", searchModel.TeacherName),
                new SqlParameter("@ClassCode", searchModel.ClassCode),
                new SqlParameter("@AreaID", searchModel.AreaID),
                new SqlParameter("@CourseID", searchModel.CourseID)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getCourseAnalysisInfo", parms))
            {
                while (dr.Read())
                {
                    CourseSurveyReportModel model = new CourseSurveyReportModel();
                    model.ClassID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.AreaName = dr["AreaName"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CurrentCount = (int)dr["CurrentCount"];
                    model.OptionList = GetCourseSurveyCategoryListByCourseID(model.CourseID);
                    model.VoteList = GetCourseAnalysisContentList(model.CourseID, model.ClassID);
                    list.Add(model);
                }
                return list;
            }
        }

        public IList<CourseSurveyReportModel> GetCourseAnalysisInfo2(string[] classid) 
        {
            IList<CourseSurveyReportModel> list = new List<CourseSurveyReportModel>();
            string sql = "SELECT a.ID, a.TeacherID, a.AreaID, a.ClassCode, b.CourseID, c.Name AS TeacherName, d.Name AS CourseName, e.Name AS AreaName, a.CurrentCount FROM E_Class AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.CourseRelationID = b.ID INNER JOIN E_Teacher AS c ON a.TeacherID = c.ID INNER JOIN E_Dict_Course AS d ON b.CourseID = d.ID INNER JOIN E_Dict_Area AS e ON a.AreaID = e.ID WHERE " + DALUtils.GetInCondition("a.ID", classid);
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseSurveyReportModel model = new CourseSurveyReportModel();
                    model.ClassID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.TeacherName = dr["TeacherName"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.AreaName = dr["AreaName"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CurrentCount = (int)dr["CurrentCount"];
                    model.OptionList = GetCourseSurveyCategoryListByCourseID(model.CourseID);
                    model.VoteList = GetCourseAnalysisContentList(model.CourseID, model.ClassID);
                    list.Add(model);
                }
                return list;
            }
        }

        public IList<CourseSurveyReportModel> GetInternalCourseAnalysisInfo2(string[] classid)
        {
            IList<CourseSurveyReportModel> list = new List<CourseSurveyReportModel>();
            string sql = "SELECT * FROM v_internal_classwithusercount WHERE " + DALUtils.GetInCondition("ID", classid);
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseSurveyReportModel model = new CourseSurveyReportModel();
                    model.ClassID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.CurrentCount = (int)dr["UserCount"];
                    model.OptionList = GetCourseSurveyCategoryListByCourseID(model.CourseID);
                    model.VoteList = GetCourseAnalysisContentList(model.CourseID, model.ClassID);
                    list.Add(model);
                }
                return list;
            }
        }

        public IList<CourseSurveyReportModel> GetPublicCourseAnalysisInfo2(string[] classid)
        {
            IList<CourseSurveyReportModel> list = new List<CourseSurveyReportModel>();
            string sql = "SELECT * FROM v_public_classwithusercount WHERE " + DALUtils.GetInCondition("ID", classid);
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseSurveyReportModel model = new CourseSurveyReportModel();
                    model.ClassID = (int)dr["ID"];
                    model.ClassCode = dr["ClassCode"].ToString();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.CurrentCount = (int)dr["UserCount"];
                    model.OptionList = GetCourseSurveyCategoryListByCourseID(model.CourseID);
                    model.VoteList = GetCourseAnalysisContentList(model.CourseID, model.ClassID);
                    list.Add(model);
                }
                return list;
            }
        }

        public IDictionary<string, string> GetCourseAnalysisContentList(int courseid, int classid)
        {
            IDictionary<string, string> list = new Dictionary<string, string>();
            string sql = "SELECT b.SurveyContent, b.TotalComment FROM E_CourseSurvey AS a INNER JOIN E_CourseSurveyLog AS b ON a.ID = b.SurveyID WHERE (a.CourseID = " + courseid + ") AND (b.UserID IN (SELECT UserID FROM E_Class2User WHERE (ClassID = " + classid + ")))";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add(dr[0].ToString(), dr[1].ToString());
                }
                return list;
            }
        }

        #endregion
    }
}
